﻿package work;

import java.io.File;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Map;
import java.util.TreeMap;

import org.apache.log4j.Logger;

import com.h819.core.db.deprecated.Oracle_std;
import com.h819.core.file.excel.jxl.ExcelUtils;

public class Aoyun {

	Logger log = Logger.getLogger(Aoyun.class);

	Connection oracle_conn = null;
	Statement oracle_stmt = null;
	ResultSet oracle_rs = null;
	Oracle_std oracle = new Oracle_std();

	private void OracleQuery() {

		String query = "select y.name as 所属区县,s.ent_name as 企业名称,s.ordre_kind 订单品种,s.write_date as 填表时间,"
				+ "t.prodauce_date as 生产日期,t.input_date 成品入库日期,"
				+ "t.input_num 成品入库数量,s.temp_field1 as 量纲,t.input_count_num 成品入库换算数量 ,"
				+ "c.check_date as 检测日期,c.sample_date as 检测编号 "
				+ "from food_produce_thing t,food_order s,of_ent e,sy_code_table y,food_check_thing c "
				+ "where s.ent_id=e.id and e.ent_regioncode=y.code and "
				+ "c.order_id=t.order_id and "
				+ "c.temp_field1=t.prodauce_date and "
				+ "t.order_id = s.id "
				//+ "and s.ent_name='北京宝罗熊袋装冰有限公司' "
				+ "order by s.ent_name,t.prodauce_date, c.sample_date ";

		oracle_conn = oracle.getConnection(oracle_conn);

		int count = 0;

		TreeMap<Integer, ArrayList<String>> mapAllLine = new TreeMap<Integer, ArrayList<String>>();
		TreeMap<Integer, ArrayList<String>> mapAllLine2 = new TreeMap<Integer, ArrayList<String>>();
		ArrayList al = new ArrayList();

		try {

			// oracle_stmt = oracle_conn.createStatement();
			// 为了支持 oracle_rs.beforeFirst() 方法，createStatement 带了参数
			// 否则 oracle_stmt = oracle_conn.createStatement() 即可
			oracle_stmt = oracle_conn.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			oracle_rs = oracle_stmt.executeQuery(query);

			if (!oracle_rs.next()) {
				// 无结果集
				// do something.
			} else {
				// 因为经过了 !oracle_rs.next() 语句,指针就不子第一位了,故用下面的方法
				// 有结果集，指针归位
				oracle_rs.beforeFirst();
			}

			while (oracle_rs.next()) {
				// System.out.println("所属区县 :" + oracle_rs.getString("所属区县"));
				// System.out.println("企业名称 :" + oracle_rs.getString("企业名称"));

				ArrayList temp = new ArrayList();
				temp.add(oracle_rs.getString("所属区县"));
				temp.add(oracle_rs.getString("企业名称"));
				temp.add(oracle_rs.getString("订单品种"));
				temp.add(oracle_rs.getString("成品入库日期"));
				temp.add(oracle_rs.getString("成品入库数量"));
				temp.add(oracle_rs.getString("量纲"));
				temp.add(oracle_rs.getString("成品入库换算数量"));
				temp.add(oracle_rs.getString("检测日期"));
				temp.add(oracle_rs.getString("检测编号"));

				mapAllLine.put(count, temp);
				count++;

				// 打印测试
				// for (Iterator it=mapAllLine.entrySet().iterator();
				// it.hasNext(); ) {
				// Map.Entry entry = (Map.Entry)it.next();
				// Object key = entry.getKey();
				// ArrayList value = (ArrayList)entry.getValue();
				//			        
				// // log.info("key:"+key);
				//			        
				//			        
				//
				// System.out.print("key:"+key);
				//			        
				// for (Iterator ita=value.iterator(); ita.hasNext(); ) {
				// String element = (String)ita.next();
				// System.out.print(element+",");
				// }
				//
				// System.out.println("");
				//			        
				// }
			}


			
			ArrayList temp3 = new ArrayList();
			temp3.add("所属区县");
			temp3.add("企业名称");
			temp3.add("订单品种");
			temp3.add("成品入库日期");
			temp3.add("成品入库数量");
			temp3.add("量纲");
			
			temp3.add("成品入库换算数量");
			temp3.add("检测日期");
			temp3.add("检测编号");
			
			al.add(temp3);
			

			
			int u =0;

			// 复制一份，便于操作
			// mapAllLine2=mapAllLine;

			while (mapAllLine.size() != 0) {
				u++;

				// 整理数据

				String y0 = "";
				String a0 = "";
				String b0 = "";
				String c0 = "";
				String d0 = "";
				String e0 = "";
				float f0 = 0;
				String g0 = "";
				String h0 = "";
				
				System.out.println("u :"+u+" size0 :" + mapAllLine.size());
					System.out.println("f0:" + f0);

				
				Iterator it = mapAllLine.entrySet().iterator();

				while (it.hasNext()) {
					Map.Entry entry = (Map.Entry) it.next();
					Object key = entry.getKey();
					ArrayList value = (ArrayList) entry.getValue();
					


					// log.info("key:"+key);

					String y = (String) value.get(0);
					if (y == null)
						System.out.println("y: " + y);

					String a = (String) value.get(1);
					if (a == null)
						System.out.println("a: " + a);
					String b = (String) value.get(2);
					if (b == null)
						System.out.println("b: " + b);
					String c = (String) value.get(3);
					if (c == null)
						System.out.println("c: " + c);
					String d = (String) value.get(4);
					if (d == null)
						System.out.println("d: " + d);
					String e = (String) value.get(5);
					if (e == null)
						System.out.println("e: " + e);
					String f = (String) value.get(6);
					if (f == null)
						System.out.println("f: " + f);
					float f1 = Float.valueOf(f).floatValue();
					String g = (String) value.get(7);
					if (g == null)
						System.out.println("g: " + g);
					String h = (String) value.get(8);
					if (h == null)
						System.out.println("h: " + h);

					y0 = y;
					a0 = a;
					b0 = b;
					c0 = c;
					d0 = d;
					e0 = e;
				
					
					f0 = f0 + f1;
					if(h!=null && h.equals("AY0807651"))
					
					g0 = g;
					h0 = h;

				//	System.out.println("key:" + key);

					// System.out.print("所属区县:" + y+", ");
					// System.out.print("企业名称:" + a+", ");
					// System.out.print("订单品种:" + b+", ");
					// System.out.print("成品入库日期:" + c+", ");
					// System.out.print("成品入库数量:" + d+", ");
					// System.out.print("量纲:" + e+", ");
					// System.out.print("成品入库换算数量:" + f1+", ");
					// System.out.print("检测日期:" + g+", ");
					// System.out.print("检测编号:" + h+", ");
					// System.out.println("");

				//	mapAllLine.remove(key);
					

					
					it.remove();
					
					// 复制一份，便于操作
					 mapAllLine2=mapAllLine;
					
					
					// 查看是否包含同样数据，经过循环之后，某个把重复的数据都剔除了
					for (Iterator it2 = mapAllLine2.entrySet().iterator(); it2
							.hasNext();) {

						try {

							Map.Entry entry2 = (Map.Entry) it2.next();
							Object key2 = entry2.getKey();
							ArrayList value2 = (ArrayList) entry2.getValue();

							String y1 = (String) value2.get(0);
							String a1 = (String) value2.get(1);
							String b1 = (String) value2.get(2);
							String c1 = (String) value2.get(3);
							String d1 = (String) value2.get(4);
							String e1 = (String) value2.get(5);
							String f11 = (String) value2.get(6);
							float f111 = Float.valueOf(f11).floatValue();
							String g1 = (String) value2.get(7);
							String h1 = (String) value2.get(8);

							// System.out.print("a:"+a+" a1:"+a1+" ");
							// System.out.print("b:"+b+" b1:"+b1+" ");
							// System.out.println("h:"+h+" h1:"+h1);

							// 如果包含了，那么就会更改数据
							if (a != null && a.equals(a1) && b != null
									&& b.equals(b1) && h != null
									&& h.equals(h1)) {

								y0 = y1;
								a0 = a1;
								b0 = b1;
								c0 = c1;
								d0 = d1;
								e0 = e1;
								f0 = f0 + f111;
								g0 = g1;
								h0 = h1;

								//mapAllLine.remove(key2);
								it2.remove();					
								continue;
							} else{
								
								continue;// 否则，不更改数据
							}
							
							

						} catch (Exception e1) {

							e1.printStackTrace();
						}

					}
					
					System.out.println("size2 :"+mapAllLine2.size());

					// mapAllLine = mapAllLine;

					ArrayList temp2 = new ArrayList();
					temp2.add(y0);
					temp2.add(a0);
					temp2.add(b0);
					temp2.add(c0);
					temp2.add(d0);
					temp2.add(e0);
					
					temp2.add(String.valueOf(f0));
					temp2.add(g0);
					temp2.add(h0);

					al.add(temp2);

					// for (Iterator ita = value.iterator(); ita.hasNext();) {
					// String element = (String) ita.next();
					// System.out.print(element + ",");
					// }
					// System.out.println("");
					break;
				}

			}

			log.info("count:" + count);
			oracle.closeDB(oracle_rs, oracle_stmt, oracle_conn);

			// 打印测试
			System.out.println("al size " + al.size());
			for (Iterator it5 = al.iterator(); it5.hasNext();) {
				ArrayList element5 = (ArrayList) it5.next();

				for (Iterator it6 = element5.iterator(); it6.hasNext();) {
					Object element6 = (Object) it6.next();

					 System.out.print(element6 + " , ");

				}

				 System.out.println("");
			}
			
			ExcelUtils eu = new ExcelUtils();

			eu.writeListListValueToExcel(new File("D://234//test1.xls"), al);
			

		} catch (SQLException ex) {
			log.info("exception in query action.");
			oracle.closeDB(oracle_rs, oracle_stmt, oracle_conn);
		}

	}

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		Aoyun ao = new Aoyun();
		ao.OracleQuery();
	}
}